﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using System.Collections;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESRoleMenuBusiness
    {
        /// <summary>
        /// 获取角色菜单信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetRoleMenuData(Dictionary<string, string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT rm.id,rd.rolename,rm.roleid,rm.menuname,rm.menuid,e.fullname,rm.createdate");
            strSql.AppendLine("FROM rolemenu rm");
            strSql.AppendLine("LEFT JOIN employee e ON e.employeeid = rm.createempid");
            strSql.AppendLine(" left join roledef rd on rd.roleid=rm.roleid ");
            strSql.AppendLine("WHERE 1=1");
            string condition = "";
            if (para.ContainsKey("menuname"))
            {
                condition += string.Format(" and rm.menuname like '%{0}%'", para["menuname"]);
            }

            if (para.ContainsKey("RoleName"))
            {
                condition += string.Format(" and rm.rolename like '%{0}%'", para["RoleName"]);
            }

            strSql.AppendLine(condition);

            strSql.AppendLine("ORDER BY rd.rolename,rm.sequence");

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            result.DBTable.Columns.Add("isDeleted");
            if (result.DBTable.Rows.Count > 0)
            {
                DataTable dtRole = GetRole();
                if (dtRole.Rows.Count > 0)
                {
                    DataRow[] dr;
                    for (int i = 0; i < result.DBTable.Rows.Count; i++)
                    {
                        string strRoleId = string.Empty;
                        result.DBTable.Rows[i]["isDeleted"] = "0";//角色未删除
                        if (!string.IsNullOrEmpty(result.DBTable.Rows[i]["roleid"].ToString()))
                        {
                            strRoleId = result.DBTable.Rows[i]["roleid"].ToString();
                            dr = dtRole.Select("roleid='" + strRoleId + "'");
                            if (dr.Length == 0)
                            {
                                result.DBTable.Rows[i]["isDeleted"] = "1";//角色已删除
                            }
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 查询角色
        /// </summary>
        /// <returns></returns>
        public DataTable GetRole()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT r.roleid,r.rolename FROM roledef r WHERE r.isdisplay = 1");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetMenu()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT m.menuname,m.id FROM menu m");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetMenuInfo(Dictionary<string,string>para)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT m.menuname,m.id ");
            strSql.AppendLine("FROM menu m");
            strSql.AppendLine("WHERE 1 = 1");
            if (para.ContainsKey("MenuName"))
            {
                strSql.AppendLine("AND LOWER(m.menuname) LIKE '%"+ para["MenuName"].ToLower() +"%'");
            }
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询角色菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleMenu(String strid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT rm.id,rd.rolename,rm.roleid,rm.menuname,rm.menuid");
            strSql.AppendLine("FROM rolemenu rm");
            strSql.AppendLine(" left join roledef rd on rd.roleid=rm.roleid ");
            strSql.AppendLine("WHERE rm.roleid='" + strid + "'");
            strSql.AppendLine(" order by rm.sequence asc ");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 删除角色菜单
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public Boolean DelRoleMenu(string strID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除 
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE rolemenu WHERE id = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            OracleHelper.ExecuteSqlTran(SQLStringList);
            return true;
        }

        /// <summary>
        /// 删除角色关联的所有菜单
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public Boolean DelRoleMenuByRoleID(string strRoleID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除 
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE rolemenu WHERE roleid = '{0}'", strRoleID));

            SQLStringList.Add(strSQL.ToString());
            OracleHelper.ExecuteSqlTran(SQLStringList);
            return true;
        }

        /// <summary>
        /// 保存人员角色
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel SaveRoleMenuInfo(Dictionary<string, string> para, DataTable dt)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList();
            int se = 0;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    StringBuilder strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO rolemenu(id,rolename,roleid,menuname,menuid,createempid,sequence,createdate)");
                    strSQL.AppendLine("VALUES(");
                    strSQL.AppendLine(string.Format("'{0}',", Guid.NewGuid().ToString()));
                    strSQL.AppendLine(string.Format("'{0}',", para["rolename"]));
                    strSQL.AppendLine(string.Format("'{0}',", para["roleid"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["menuname"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["menuid"]));
                    strSQL.AppendLine(string.Format("'{0}',", para["CreateEmployeeID"]));
                    strSQL.AppendLine(string.Format("{0},", ++se));
                    strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["CreateDate"]));
                    strSQL.AppendLine(")");
                    SQLStringList.Add(strSQL.ToString());
                }
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;
            return result;
        }


        /// <summary>
        /// 更新人员角色
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel UpdateRoleMenu(Dictionary<string, string> para, DataTable dt)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE rolemenu WHERE roleid = '{0}'", para["roleid"].ToString()));
            SQLStringList.Add(strSQL.ToString());
            int se = 0;
            foreach (DataRow row in dt.Rows)
            {                
                strSQL = new StringBuilder();
                strSQL.AppendLine("INSERT INTO rolemenu(id,rolename,roleid,menuname,menuid,createempid,sequence,createdate)");
                strSQL.AppendLine("VALUES(");
                strSQL.AppendLine(string.Format("'{0}',", Guid.NewGuid().ToString()));
                strSQL.AppendLine(string.Format("'{0}',", para["rolename"]));
                strSQL.AppendLine(string.Format("'{0}',", para["roleid"]));
                strSQL.AppendLine(string.Format("'{0}',", row["menuname"]));
                strSQL.AppendLine(string.Format("'{0}',", row["menuid"]));
                strSQL.AppendLine(string.Format("'{0}',", para["CreateEmployeeID"]));
                strSQL.AppendLine(string.Format("{0},", ++se));
                strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["CreateDate"]));
                strSQL.AppendLine(")"); 
                SQLStringList.Add(strSQL.ToString());
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;
            return result;
        }


        /// <summary>
        /// 查询登录人的角色 
        /// </summary>
        /// <returns></returns>
        public DataTable GetEmployeeRole(String strEmployeeid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT re.rolename,re.roleid,re.employeeid FROM roleemployee re"); 
            strSql.AppendLine("WHERE re.employeeid='" + strEmployeeid + "'");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询角色菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleMenuData(String strRoleidList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT Distinct rm.menuname,rm.menuid,rm.roleid,rm.sequence FROM rolemenu rm");
            strSql.AppendLine("WHERE rm.roleid in (" + strRoleidList  + ")");
            strSql.AppendLine(" order by rm.roleid,rm.sequence asc  ");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询子菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetMenuChildData(String strMenuidList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT mc.menuchildname, mc.url, mc.sequence,mc.mainid FROM menuchild mc");
            strSql.AppendLine("WHERE mc.mainid in (" + strMenuidList + ") ORDER BY mc.sequence");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }
        
    }

}
